CAdoFields Class

Contains all the Field objects of a Recordset or Record object.

Include file: CADOFields.inc (include CADODB.inc)

A Recordset object has a Fields collection made up of Field objects. Each Field object corresponds to a column in the Recordset. You can populate the Fields collection before opening the Recordset by calling the Refresh method on the collection.

See the Field object topic for a more detailed explanation of how to use Field objects.

The Fields collection has an Append method, which provisionally creates and adds a Field object to the collection, and an Update method, which finalizes any additions or deletions.

A Record object has two special fields that can be indexed with FieldEnum constants. One constant accesses a field containing the default stream for the Record, and the other accesses a field containing the absolute URL string for the Record.

Certain providers (for example, the Microsoft OLE DB Provider for Internet Publishing) may populate the Fields collection with a subset of available fields for the Record or Recordset. Other fields will not be added to the collection until they are first referenced by name or indexed by your code.

If you attempt to reference a nonexistent field by name, a new Field object will be appended to the Fields collection with a Status of adFieldPendingInsert. When you call Update, ADO will create a new field in your data source if allowed by your provider.

Methods and Properties

CADOField Class

Represents a column of data with a common data type.

Include file: CADOField.inc (include CADODB.inc)

Each Field object corresponds to a column in the Recordset. You use the Value property of Field objects to set or return data for the current record. Depending on the functionality the provider exposes, some collections, methods, or properties of a Field object may not be available.

With the collections, methods, and properties of a Field object, you can do the following:

• Return the name of a field with the Name property.
• View or change the data in the field with the Value property. Value is the default property of the Field object.
• Return the basic characteristics of a field with the Type_, Precision, and NumericScale properties.
• Return the declared size of a field with the DefinedSize property.
• Return the actual size of the data in a given field with the ActualSize property.
• Determine what types of functionality are supported for a given field with the Attributes property and Properties collection.
• Manipulate the values of fields containing long binary or long character data with the AppendChunk and GetChunk methods.
• If the provider supports batch updates, resolve discrepancies in field values during batch updating with the OriginalValue and UnderlyingValue properties.

All of the metadata properties (Name, Type_, DefinedSize, Precision, and NumericScale) are available before opening the Field object’s Recordset. Setting them at that time is useful for dynamically constructing forms.

Methods and Properties

Append (CADOFields)

Appends an object to a collection. A new Field object may be created before it is appended to the collection.

FUNCTION Append (BYREF wszName AS WSTRING, BYVAL nType AS DataTypeEnum, _
   BYVAL DefinedSize AS ADO_LONGPTR = 0, BYVAL Attrib AS FieldAttributeEnum = 0, _
   BYREF dvFieldValue AS DVARIANT = TYPE<VARIANT>(VT_ERROR,0,0,0,DISP_E_PARAMNOTFOUND)) AS HRESULT

Return value

S_OK (0) or an HRESULT code.

DataTypeEnum

Specifies the data type of a Field, Parameter, or Property. The corresponding OLE DB type indicator is shown in parentheses in the description column of the following table.

Remarks

The FieldValue parameter is only valid when adding a Field object to a Record object, not to a Recordset object. With a Record object, you may append fields and provide values at the same time. With a Recordset object, you must create fields while the Recordset is closed, then open the Recordset and assign values to the fields.

Notes: For new Field objects that have been appended to the Fields collection of a Record object, the **Value*+ property must be set before any other Field properties can be specified. First, a specific value for the Value property must have been assigned and Update on the Fields collection called. Then, other properties such as Type or Attributes can be accessed.

Field objects of the following data types (DataTypeEnum) cannot be appended to the Fields collection and will cause an error to occur: adArray, adChapter, adEmpty, adPropVariant, and adUserDefined. Also, the following data types are not supported by ADO: adIDispatch, adIUnknown, and adIVariant. For these types, no error will occur when appended, but usage can produce unpredictable results including memory leaks.

Attach (CADOFields)

Attaches a reference to an ADO Fields object to the class.

SUB Attach (BYVAL pFields AS Afx_ADOFields PTR, BYVAL fAddRef AS BOOLEAN = FALSE)

CancelUpdate (CADOFields)

Cancels any changes made to the Fields collection of a Record object before calling the Update method.

FUNCTION CancelUpdate () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

The CancelUpdate method cancels any pending insertions or deletions of Field objects, and cancels pending updates of existing fields and restores them to their original values. The Status property of all fields in the Fields collection is set to adFieldOK.

Count (CADOFields)

Retrieves the number of objects in the Fields collection.

PROPERTY Count () AS LONG

Return value

The number of objects in the Fields collection.

Remarks

Because numbering for members of a collection begins with zero, you should always code loops starting with the zero member and ending with the value of the Count property minus 1.

If the Count property is zero, there are no objects in the collection.

Delete_ (CADOFields)

Deletes an object from the Fields collection.

FUNCTION Delete_ (BYREF dvIndex AS DVARIANT) AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Calling the Delete_ method on an open Recordset causes a run-time error.

Item (CADOFields)

Indicates a specific member of the Fields collection, by name or ordinal number.

PROPERTY Item (BYREF dvIndex AS DVARIANT) AS Afx_ADOField PTR

Return value

An ADO Fields object reference.

Remarks

If Item cannot find an object in the collection corresponding to the Index argument, an error occurs.

Refresh (CADOFields)

Refreshes the contents of the Fields collection.

FUNCTION Refresh () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Using the Refresh method on the Fields collection has no visible effect. To retrieve changes from the underlying database structure, you must use either the Requery method or, if the Recordset object does not support bookmarks, the MoveFirst method.

Resync (CADOFields)

Resynchronizes the contents of the Fields collection.

FUNCTION Resync (BYVAL ResyncValues AS ResyncEnum = adResyncAllValues) AS HRESULT

ResyncEnum

Specifies whether underlying values are overwritten by a call to Resync.

Return value

S_OK (0) or an HRESULT code.

Remarks

Use the Resync method to resynchronize the values of the Fields collection of a Record object with the underlying data source. The Count property is not affected by this method.

If ResyncValues is set to adResyncAllValues (the default value), then the UnderlyingValue, Value, and OriginalValue properties of Field objects in the collection are synchronized. If ResyncValues is set to adResyncUnderlyingValues, only the UnderlyingValue property is synchronized.

The value of the Status property for each Field object at the time of the call also affects the behavior of Resync. For Field objects with Status values of adFieldPendingUnknown or adFieldPendingInsert, Resync has no effect. For Status values of adFieldPendingChange or adFieldPendingDelete, Resync synchronizes data values for fields that still exist at the data source.

Resync will not modify Status values of Field objects unless an error occurs when Resync is called. For example, if the field no longer exists, the provider will return an appropriate Status value for the Field object, such as adFieldDoesNotExist. Returned Status values may be logically combined within the value of the Status property.

Update (CADOFields)

Saves any changes you make to the current Fields collection of a Record object.

FUNCTION Update () AS HRESULT

Return value

S_OK or an HRESULT code.

Remarks

The Update method finalizes additions, deletions, and updates to fields in the Fields collection of a Record object.

For example, fields deleted with the Delete_ method are marked for deletion immediately but remain in the collection. The Update method must be called to actually delete these fields from the provider’s collection.

ActualSize (CADOField)

Indicates the actual length of a field’s value. Some providers may allow this property to be set to reserve space for BLOB data, in which case the default value is 0.

PROPERTY ActualSize () AS ADO_LONGPTR

Return value

The actual length of the field’s value.

Remarks

Use the ActualSize property to return the actual length of a field value. For all fields, the ActualSize property is read-only. If ADO cannot determine the length of the field value, the ActualSize property returns adUnknown.

The ActualSize and DefinedSize properties are different. A field with a declared type of adVarChar and a maximum length of 50 characters returns a DefinedSize property value of 50, but the ActualSize property value it returns is the length of the data stored in the field for the current record. Fields with a DefinedSize greater than 255 bytes are treated as variable length columns.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // Set the cursor location
DIM pRecordset AS CAdoRecordset
pRecordset.CursorLocation = adUseClient

' // Open the recordset
DIM dvSource AS DVARIANT = "SELECT * FROM Publishers"
pRecordset.Open(dvSource, pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // Get a reference to the Fields collection
DIM pFields AS CAdoFields = pRecordset.Fields

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the contents of the fields
   DIM pField AS CAdoField
   pField.Attach(pFields.Item("Name"))
   DIM dvRes AS DVARIANT = pField.Value
   PRINT "Name: "; dvRes; " - "; WSTR(pField.ActualSize); " - "; WSTR(pField.DefinedSize)
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

AppendChunk (CADOField)

Appends data to a large text or binary data Field.

FUNCTION AppendChunk (BYREF dvData AS DVARIANT) AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Use the AppendChunk method on a Field object to fill it with long binary or character data. In situations where system memory is limited, you can use the AppendChunk method to manipulate long values in portions rather than in their entirety.

If the adFldLong bit in the Attributes property of a Field object is set to true, you can use the AppendChunk method for that field.

The first AppendChunk call on a Field object writes data to the field, overwriting any existing data. Subsequent AppendChunk calls add to existing data. If you are appending data to one field and then you set or read the value of another field in the current record, ADO assumes that you are finished appending data to the first field. If you call the AppendChunk method on the first field again, ADO interprets the call as a new AppendChunk operation and overwrites the existing data. Accessing fields in other Recordset objects that are not clones of the first Recordset object will not disrupt AppendChunk operations.

If there is no current record when you call AppendChunk on a Field object, an error occurs.

Note: The AppendChunk method does not operate on Field objects of a Record object. It does not perform any operation and will produce a run-time error.

Attach (CADOField)

Attaches a reference to an ADO Field object to the class.

SUB Attach (BYVAL pField AS Afx_ADOField PTR, BYVAL fAddRef AS BOOLEAN = FALSE)

Attributes (CADOField)

Indicates one or more characteristics of a field.

PROPERTY Attributes () AS LONG
PROPERTY Attributes (BYVAL lAttr AS LONG)

Return value

One or more FieldAttributeEnum values.

FieldAttributeEnum

Specifies one or more attributes of a Field object.

Remarks

When you set multiple attributes, you can sum the appropriate constants. If you set the property value to a sum including incompatible constants, an error occurs.

Remote Data Service Usage

This property is not available on a client-side Connection object.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // Open the recordset
DIM pRecordset AS CAdoRecordset
DIM dvSource AS DVARIANT = "SELECT * FROM Publishers ORDER BY PubID"
DIM hr AS HRESULT = pRecordset.Open(dvSource, pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // Get a reference to the Fields collection
DIM pFields AS CAdoFields = pRecordset.Fields
DIM nCount AS LONG = pFields.Count
IF nCount THEN
   PRINT
   PRINT "Nullable fields:"
   PRINT "================"
   PRINT
   FOR i AS LONG = 0 TO nCount - 1
      DIM pField AS CAdoField
      pField.Attach(pFields.Item(i))
      ' // Get the attributes of the field
      DIM lAttr AS LONG = pField.Attributes
      ' // Display fields that are nullable
      IF (lAttr AND adFldIsNullable) = adFldIsNullable THEN
         PRINT "Field name: "; pField.Name
      END IF
   NEXT
END IF

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

DataFormat (CADOField)

Links the current Field object to a data-bound control.

PROPERTY DataFormat () AS IUnknown PTR
PROPERTY DataFormat (BYVAL piDF AS IUnknown PTR)

Return value

A reference to a StdDataFormat object.

Remarks

The DataFormat property is both read- and write-enabled. It accepts and returns a StdDataFormat object that is used to attach a bound object.

DefinedSize (CADOField)

Indicates the data capacity of a field.

PROPERTY DefinedSize () AS LONG
PROPERTY DefinedSize (BYVAL lSize AS LONG)

Return value

The actual length of the field’s value.

Remarks

Use the DefinedSize property to determine the data capacity of a field.

The DefinedSize and ActualSize properties are different. For example, consider a field with a declared type of adVarChar and a DefinedSize property value of 50, containing a single character. The ActualSize property value it returns is the length in bytes of the single character.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // === Set the cursor location
DIM pRecordset AS CAdoRecordset
pRecordset.CursorLocation = adUseClient

' // Open the recordset
DIM dvSource AS DVARIANT = "SELECT * FROM Publishers"
pRecordset.Open(dvSource, pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // Get a reference to the Fields collection
DIM pFields AS CAdoFields = pRecordset.Fields

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the contents of the fields
   DIM pField AS CAdoField
   pField.Attach(pFields.Item("Name"))
   DIM dvRes AS DVARIANT = pField.Value
   PRINT "Name: "; dvRes; " - "; WSTR(pField.ActualSize); " - "; WSTR(pField.DefinedSize)
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

GetChunk (CADOField)

Returns all, or a portion, of the contents of a large text or binary data field.

FUNCTION GetChunk (BYVAL nLen AS LONG) AS DVARIANT

Return value

The retrieved data.

Remarks

Use the GetChunk method on a Field object to retrieve part or all of its long binary or character data. In situations where system memory is limited, you can use the GetChunk method to manipulate long values in portions, rather than in their entirety.

The data that a GetChunk call returns is assigned to variable. If Size is greater than the remaining data, the GetChunk method returns only the remaining data without padding variable with empty spaces. If the field is empty, the GetChunk method returns a null value.

Each subsequent GetChunk call retrieves data starting from where the previous GetChunk call left off. However, if you are retrieving data from one field and then you set or read the value of another field in the current record, ADO assumes you have finished retrieving data from the first field. If you call the GetChunk method on the first field again, ADO interprets the call as a new GetChunk operation and starts reading from the beginning of the data. Accessing fields in other Recordset objects that are not clones of the first Recordset object will not disrupt GetChunk operations.

If the adFldLong bit in the Attributes property of a Field object is set to True, you can use the GetChunk method for that field.

If there is no current record when you use the GetChunk method on a Field object, error 3021 (no current record) occurs.

Note: The GetChunk method does not operate on Field objects of a Record object. It does not perform any operation and will produce a run-time error.

Name (CADOField)

Returns the name of the field.

PROPERTY Name () AS DWSTRING

NumericScale (CADOField)

Sets or returns a Byte value that indicates the number of decimal places to which numeric values will be resolved.

PROPERTY NumericScale () AS BYTE
PROPERTY NumericScale (BYVAL bScale AS BYTE)

Return value

The number of decimal places. Returns -1 if it is not a numeric field.

Remarks

Use the NumericScale property to determine how many digits to the right of the decimal point will be used to represent values for a numeric field.

For a field, NumericScale is normally read-only. However, for new fields that have been appended to a Record, NumericScale is read/write only after the Value property for the field has been specified and the data provider has successfully added the new field by calling the Update method of the Fields collection.

OriginalValue (CADOField)

Indicates the value of a field that existed in the record before any changes were made.

PROPERTY OriginalValue () AS DVARIANT

Return value

The original field value.

Remarks

Use the OriginalValue property to return the original field value for a field from the current record.

In immediate update mode (in which the provider writes changes to the underlying data source after you call the Update method), the OriginalValue property returns the field value that existed prior to any changes (that is, since the last Update method call). This is the same value that the CancelUpdate method uses to replace the Value property.

In batch update mode (in which the provider caches multiple changes and writes them to the underlying data source only when you call the UpdateBatch method), the OriginalValue property returns the field value that existed prior to any changes (that is, since the last UpdateBatch method call). This is the same value that the CancelBatch method uses to replace the Value property. When you use this property with the UnderlyingValue property, you can resolve conflicts that arise from batch updates.

Name (CADOField)

Returns the name of the field.

PROPERTY Name () AS DWSTRING

Precision (CADOField)

Sets or returns a Byte value that indicates the maximum number of digits used to represent values.

PROPERTY Precision () AS BYTE
PROPERTY Precision (BYVAL bPrecision AS BYTE)

Return value

The degrees of precision. Returns -1 if it is not a numeric field.

Remarks

Use the Precision property to determine the maximum number of digits used to represent values for a numeric parameter or field.

For a field, Precision is normally read-only. However, for new fields that have been appended to a Record, Precision is read/write only after the Value property for the field has been specified and the data provider has successfully added the new field by calling the Update method of the Fields collection.

Properties (CADOField)

Returns a reference to the Properties collection.

PROPERTY Properties () AS Afx_ADOProperties

Return value

An ADO Properties object reference.

Status (CADOField)

Indicates the status of a field. Returns a FieldStatusEnum value.

PROPERTY Status () AS LONG

Return value

A FieldStatusEnum value. The default value is adFieldOK.

FieldStatusEnum

Specifies the status of a Field object.

The adFieldPending* values indicate the operation that caused the status to be set, and may be combined with other status values.

Remarks

Changes to the value of a Field object in the Fields collection of a Record object are cached until the object’s Update method is called. At that point, if the change to the Field’s value caused an error, OLE DB raises the error DB_E_ERRORSOCCURRED (2147749409). The Status property of any of the Field objects in the Fields collection that caused the error will contain a value from the FieldStatusEnum describing the cause of the problem.

To enhance performance, additions and deletions to the Fields collections of the Record object are cached until the Update method is called, and then the changes are made in a batch optimistic update. If the Update method is not called, the server is not updated. If any updates fail then an OLE DB provider error (DB_E_ERRORSOCCURRED) is returned and the Status property indicates the combined values of the operation and error status code. For example, adFieldPendingInsert OR adFieldPermissionDenied. The Status property for each Field can be used to determine why the Field was not added, modified, or deleted.

Many types of problems encountered when adding, modifying, or deleting a Field are reported through the Status property. For example, if the user deletes a Field, it is marked for deletion from the Fields collection. If the subsequent Update returns an error because the user tried to delete a Field for which they do not have permission, the Field will have a Status of adFieldPermissionDenied or adFieldPendingDelete. Calling the CancelUpdate method restores original values and sets the Status to adFieldOK.

Similarly, the Update method may return an error because a new Field was added and given an inappropriate value. In that case the new Field will be in the Fields collection and have a status of adFieldPendingInsert and perhaps adFieldCantCreate (depending upon your provider). You can supply an appropriate value for the new Field and call Update again.

Recordset Field Status

Changes to the value of a Field object in the Fields collection of either a Recordset are cached until the object’s Update method is called. At that point, if the change to the Field’s value caused an error, OLE DB raises the error DB_E_ERRORSOCCURRED (2147749409). The Status property of any of the Field objects in the Fields collection that caused the error will contain a value from the FieldStatusEnum describing the cause of the problem.

Type_ (CADOField)

Sets or returns a DataTypeEnum value.

PROPERTY Type_ () AS DataTypeEnum
PROPERTY Type_ (BYVAL nType AS DataTypeEnum)

Return value

A DataTypeEnum value.

DataTypeEnum

Specifies the data type of a Field, Parameter, or Property. The corresponding OLE DB type indicator is shown in parentheses in the description column of the following table.

Remarks

For new fields that have been appended to the Fields collection of a Record, Type_ is read/write only after the Value property for the field has been specified and the data provider has successfully added the new field by calling the Update method of the Fields collection.

For all other objects, the Type_ property is read-only.

UnderlyingValue (CADOField)

Returns the current field’s value in the database.

PROPERTY UnderlyingValue () AS DVARIANT

Value (CADOField)

Sets or returns a Variant value that indicates the value of the object

PROPERTY Value () AS DVARIANT
PROPERTY Value (BYREF dvValue AS DVARIANT)

Return value

The value of the field.

Remarks

The ADO Recorset object exposes a hidden member: the Collect property. This property is functionally similar to the Field’s Value property, but it doesn’t need a reference (explicit or implicit) to the Field object. You can pass either a numeric index or a field’s name to this property.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // Open the recordset
DIM pRecordset AS CAdoRecordset
DIM dvSource AS DVARIANT = "SELECT * FROM Publishers ORDER BY PubID"
DIM hr AS HRESULT = pRecordset.Open(dvSource, pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // Get a reference to the Fields collection
DIM pFields AS CAdoFields = pRecordset.Fields

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the contents of the fields
   ' // Note: Instead of pField.Attach / pField.Value, we can use pRecordset.Collect
    DIM pField AS CAdoField
    pField.Attach(pFields.Item("PubID"))
    DIM dvRes1 AS DVARIANT = pField.Value
    pField.Attach(pFields.Item("Name"))
    DIM dvRes2 AS DVARIANT = pField.Value
    pField.Attach(pFields.Item("Company Name"))
    DIM dvRes3 AS DVARIANT = pField.Value
    PRINT dvRes1; " "; dvRes2; " "; dvRes3
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close